﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections;
using System.Data.Common;
using System.Data;
using System.IO;
using System.Xml;
using Yogesh.ExcelXml;
//using System.Data.SQLite;
using System.Globalization;

/// <summary>
/// Summary description for ResourceImporter
/// </summary>
public class ResourceImporter {
    private static Regex rx = new Regex(@"\\[uU]([0-9a-f]{4})", RegexOptions.IgnoreCase);
    
    public ResourceImporter() {}

    /// <summary>
    /// Imports from a ResourceBlender XML backup.
    /// </summary>
    /// <param name="log">A delegate to which log message should be sent.</param>
    /// <param name="doc">The document to import.</param>
    /// <param name="clearDatabase"><c>true</c> if the database should be erased before starting (if elements are present in that table)</param>
    /// <param name="includeApps"><c>true</c> to include applications, otherwise<c>false</c></param>
    /// <param name="includeElements"><c>true</c> to include elements, otherwise<c>false</c></param>
    /// <param name="includeResources"><c>true</c> to include resources, otherwise<c>false</c></param>
    /// <param name="includeUsers"><c>true</c> to include users, otherwise<c>false</c></param>
    /// <param name="includeTemplates"><c>true</c> to include templates, otherwise<c>false</c></param>
    public static void ImportXMLBackup(StatusMessage.StatusChangedEventHandler log, XmlDocument doc, bool clearDatabase, bool includeApps, bool includeElements, bool includeResources, bool includeUsers, bool includeTemplates) {
        int resApps = 0,
            resBundles = 0,
            resElements = 0,
            resResources = 0,
            resTemplates = 0,
            resTranslations = 0,
            resUsers = 0;
        Database db = null;
        try {
            db = new Database();
            db.OpenConnection();
            if(clearDatabase) {
                try {
                    db.DropTables(includeApps, includeElements, includeResources, includeResources, includeUsers, includeTemplates);
                } catch { }
                db.CreateTables(includeApps, includeElements, includeResources, includeResources, includeUsers, includeTemplates);
            }
            using(DbTransaction trans = db.BeginTransaction()) {
                if(includeApps) {
                    log(new StatusMessage("Restoring applications...", StatusMessage.StatusMessageType.Status));
                    using(Database.Command cmd = db.CreateCommand(trans)) {
                        cmd.CommandText = "INSERT INTO " + db.TablePrefix + "applications (id, name, displayname, defaultlanguage, languages) VALUES (@id, @name, @displayname, @defaultlanguage, @languages)";
                        cmd.AddParameterWithValue("@id", string.Empty);
                        cmd.AddParameterWithValue("@name", string.Empty);
                        cmd.AddParameterWithValue("@displayname", string.Empty);
                        cmd.AddParameterWithValue("@defaultlanguage", string.Empty);
                        cmd.AddParameterWithValue("@languages", string.Empty);
                        XmlNodeList appsNodes = doc.GetElementsByTagName("applications")[0].ChildNodes;
                        foreach(XmlNode app in appsNodes) {
                            string id = string.Empty,
                                   name = string.Empty,
                                   displayname = string.Empty,
                                   defaultlanguage = string.Empty,
                                   languages = string.Empty;
                            foreach(XmlNode property in app) {
                                switch(property.Name.ToLower()) {
                                    case "id":
                                        id = HttpUtility.HtmlDecode(property.InnerText);
                                        break;
                                    case "name":
                                        name = Utils.HtmlDecode(property.InnerText);
                                        break;
                                    case "displayname":
                                        displayname = Utils.HtmlDecode(property.InnerText);
                                        break;
                                    case "defaultlanguage":
                                        defaultlanguage = HttpUtility.HtmlDecode(property.InnerText);
                                        break;
                                    case "languages":
                                        languages = HttpUtility.HtmlDecode(property.InnerText.Trim());
                                        break;
                                }
                            }
                            if(string.IsNullOrEmpty(id) ||
                               string.IsNullOrEmpty(name) ||
                               string.IsNullOrEmpty(displayname) ||
                               string.IsNullOrEmpty(defaultlanguage) ||
                               string.IsNullOrEmpty(languages)) {
                                log(new StatusMessage("Invalid application " + displayname, StatusMessage.StatusMessageType.Error));
                            } else {
                                cmd.Parameters["@id"].Value = id;
                                cmd.Parameters["@name"].Value = name;
                                cmd.Parameters["@displayname"].Value = displayname;
                                cmd.Parameters["@defaultlanguage"].Value = defaultlanguage;
                                cmd.Parameters["@languages"].Value = languages;
                                try {
                                    cmd.ExecuteNonQuery();
                                    resApps++;
                                } catch(Exception ex) {
                                    if(ex.Message.ToLowerInvariant().Contains("unique")) {
                                        log(new StatusMessage("Application already exists: " + name, StatusMessage.StatusMessageType.Error));
                                    } else {
                                        log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                                    }
                                }
                            }
                        }
                    }
                    log(new StatusMessage("Restoring bundles...", StatusMessage.StatusMessageType.Status));
                    using(Database.Command cmd = db.CreateCommand(trans)) {
                        cmd.CommandText = "INSERT INTO " + db.TablePrefix + "bundles (id, name, application) VALUES (@id, @name, @application)";
                        cmd.AddParameterWithValue("@id", string.Empty);
                        cmd.AddParameterWithValue("@name", string.Empty);
                        cmd.AddParameterWithValue("@application", string.Empty);
                        XmlNodeList bundlesNodes = doc.GetElementsByTagName("bundles")[0].ChildNodes;
                        foreach(XmlNode bundle in bundlesNodes) {
                            string id = string.Empty,
                                   name = string.Empty,
                                   application = string.Empty;
                            foreach(XmlNode property in bundle) {
                                switch(property.Name.ToLower()) {
                                    case "id":
                                        id = HttpUtility.HtmlDecode(property.InnerText);
                                        break;
                                    case "name":
                                        name = Utils.HtmlDecode(property.InnerText);
                                        break;
                                    case "application":
                                        application = Utils.HtmlDecode(property.InnerText);
                                        break;
                                }
                            }
                            if(string.IsNullOrEmpty(id) ||
                               string.IsNullOrEmpty(name) ||
                               string.IsNullOrEmpty(application)) {
                                log(new StatusMessage("Invalid bundle " + name, StatusMessage.StatusMessageType.Error));
                            } else {
                                cmd.Parameters["@id"].Value = id;
                                cmd.Parameters["@name"].Value = name;
                                cmd.Parameters["@application"].Value = application;
                                try {
                                    cmd.ExecuteNonQuery();
                                    resBundles++;
                                } catch(Exception ex) {
                                    if(ex.Message.ToLowerInvariant().Contains("unique")) {
                                        log(new StatusMessage("Bundle already exists: " + name, StatusMessage.StatusMessageType.Error));
                                    } else {
                                        log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                                    }
                                }
                            }
                        }
                    }
                }
                if(includeElements) {
                    log(new StatusMessage("Restoring elements...", StatusMessage.StatusMessageType.Status));
                    using(Database.Command cmd = db.CreateCommand(trans)) {
                        cmd.CommandText = "INSERT INTO " + db.TablePrefix + "elements (applicationid, bundle, elementname, resourceid, comment) VALUES (@applicationid, @bundle, @elementname, @resourceid, @comment)";
                        cmd.AddParameterWithValue("@applicationid", string.Empty);
                        cmd.AddParameterWithValue("@bundle", string.Empty);
                        cmd.AddParameterWithValue("@elementname", string.Empty);
                        cmd.AddParameterWithValue("@resourceid", string.Empty);
                        cmd.AddParameterWithValue("@comment", string.Empty);
                        XmlNodeList elementsNodes = doc.GetElementsByTagName("element");
                        int numNodes = elementsNodes.Count,
                            logIncrements = numNodes >= 5 ? numNodes / 5 : 1;
                        foreach(XmlNode element in elementsNodes) {
                            string applicationid = string.Empty,
                                   bundle = string.Empty,
                                   elementname = string.Empty,
                                   resourceid = string.Empty,
                                   comment = string.Empty;
                            foreach(XmlNode property in element) {
                                switch(property.Name.ToLower()) {
                                    case "applicationid":
                                        applicationid = HttpUtility.HtmlDecode(property.InnerText);
                                        break;
                                    case "bundle":
                                        bundle = Utils.HtmlDecode(property.InnerText);
                                        break;
                                    case "elementname":
                                        elementname = Utils.HtmlDecode(property.InnerText);
                                        break;
                                    case "resourceid":
                                    case "translationid":
                                        resourceid = HttpUtility.HtmlDecode(property.InnerText);
                                        break;
                                    case "comment":
                                        comment = Utils.HtmlDecode(property.InnerText);
                                        break;
                                }
                            }
                            if(string.IsNullOrEmpty(applicationid) ||
                               string.IsNullOrEmpty(elementname)) {
                                log(new StatusMessage("Invalid element " + elementname, StatusMessage.StatusMessageType.Error));
                            } else {
                                cmd.Parameters["@applicationid"].Value = applicationid;
                                cmd.Parameters["@bundle"].Value = bundle;
                                cmd.Parameters["@elementname"].Value = elementname;
                                cmd.Parameters["@resourceid"].Value = resourceid;
                                cmd.Parameters["@comment"].Value = comment;
                                try {
                                    cmd.ExecuteNonQuery();
                                    resElements++;
                                    if(logIncrements > 0 && resElements % logIncrements == 0) {
                                        log(new StatusMessage(string.Format("Restored {0}/{1} elements", resElements, numNodes), StatusMessage.StatusMessageType.Status));
                                    }
                                } catch(Exception ex) {
                                    if(ex.Message.ToLowerInvariant().Contains("unique")) {
                                        log(new StatusMessage("Element already exists: " + elementname, StatusMessage.StatusMessageType.Error));
                                    } else {
                                        log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                                    }
                                }
                            }
                        }
                    }
                }
                if(includeResources) {
                    log(new StatusMessage("Restoring resources...", StatusMessage.StatusMessageType.Status));
                    using(Database.Command cmdInsertResource = db.CreateCommand(trans))
                    using(Database.Command cmdInsertComment = db.CreateCommand(trans)) {
                        cmdInsertResource.CommandText = "INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@id, @language, @translation, @comment)";
                        cmdInsertResource.AddParameterWithValue("@id", string.Empty);
                        cmdInsertResource.AddParameterWithValue("@language", string.Empty);
                        cmdInsertResource.AddParameterWithValue("@translation", string.Empty);
                        cmdInsertResource.AddParameterWithValue("@comment", string.Empty);
                        cmdInsertComment.CommandText = "INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@id, @comment)";
                        cmdInsertComment.AddParameterWithValue("@id", string.Empty);
                        cmdInsertComment.AddParameterWithValue("@comment", string.Empty);
                        List<XmlNode> resourceNodes = new List<XmlNode>(); // xml format changed in > 1.3
                        foreach(XmlNode oldNode in doc.GetElementsByTagName("resource")) {
                            resourceNodes.Add(oldNode);
                        }
                        foreach(XmlNode oldNode in doc.GetElementsByTagName("translation")) {
                            resourceNodes.Add(oldNode);
                        }
                        int numNodes = resourceNodes.Count,
                            logIncrements = numNodes >= 10 ? numNodes / 10 : 1;
                        foreach(XmlNode resource in resourceNodes) {
                            try {
                                string id = resource["id"].InnerText,
                                       comment = Utils.HtmlDecode(resource["comment"].InnerText),
                                       language = string.Empty;
                                XmlNodeList ndLanguages = resource.SelectNodes("languages/language");
                                if(string.IsNullOrEmpty(id) || ndLanguages.Count == 0) {
                                    log(new StatusMessage("Invalid resource", StatusMessage.StatusMessageType.Error));
                                    continue;
                                }
                                if(!string.IsNullOrEmpty(comment)) {
                                    cmdInsertComment.Parameters["@id"].Value = id;
                                    cmdInsertComment.Parameters["@comment"].Value = comment;
                                    cmdInsertComment.ExecuteNonQuery();
                                }
                                cmdInsertResource.Parameters["@id"].Value = id;
                                foreach(XmlNode ndLanguage in ndLanguages) {
                                    if(ndLanguage.Attributes.Count > 0) {
                                        cmdInsertResource.Parameters["@language"].Value = ndLanguage.Attributes[0].Value;
                                        cmdInsertResource.Parameters["@translation"].Value = Utils.HtmlDecode(ndLanguage.InnerText);
                                        if(ndLanguage.Attributes.Count > 1) {
                                            cmdInsertResource.Parameters["@comment"].Value = Utils.HtmlDecode(ndLanguage.Attributes[1].InnerText);
                                        } else {
                                            cmdInsertResource.Parameters["@comment"].Value = DBNull.Value;
                                        }
                                        try {
                                            cmdInsertResource.ExecuteNonQuery();
                                            resTranslations++;
                                        } catch(Exception ex) {
                                            log(new StatusMessage("Could not add resource for resource id " + id + ": " + ex.Message, StatusMessage.StatusMessageType.Error));
                                        }
                                    }
                                }
                                resResources++;
                                if(logIncrements > 0 && resResources % logIncrements == 0) {
                                    log(new StatusMessage(string.Format("Restored {0}/{1} resources", resResources, numNodes), StatusMessage.StatusMessageType.Status));
                                }
                            } catch(Exception ex) {
                                log(new StatusMessage("Invalid resource" + ex.Message, StatusMessage.StatusMessageType.Error));
                            }
                        }
                    }
                }
                if(includeTemplates) {
                    log(new StatusMessage("Restoring templates...", StatusMessage.StatusMessageType.Status));
                    using(Database.Command cmd = db.CreateCommand(trans)) {
                        cmd.CommandText = "INSERT INTO " + db.TablePrefix + "templates (name, filename, filestart, fileend, line) VALUES (@name, @filename, @filestart, @fileend, @line)";
                        cmd.AddParameterWithValue("@name", string.Empty);
                        cmd.AddParameterWithValue("@filename", string.Empty);
                        cmd.AddParameterWithValue("@filestart", string.Empty);
                        cmd.AddParameterWithValue("@fileend", string.Empty);
                        cmd.AddParameterWithValue("@line", string.Empty);
                        XmlNodeList templatesNodes = doc.GetElementsByTagName("template");
                        foreach(XmlNode app in templatesNodes) {
                            string name = string.Empty,
                                   filename = string.Empty,
                                   filestart = string.Empty,
                                   fileend = string.Empty,
                                   line = string.Empty;
                            foreach(XmlNode property in app) {
                                switch(property.Name.ToLower()) {
                                    case "name":
                                        name = Utils.HtmlDecode(property.InnerText);
                                        break;
                                    case "filename":
                                        filename = Utils.HtmlDecode(property.InnerText);
                                        break;
                                    case "filestart":
                                        filestart = Utils.HtmlDecode(property.InnerText);
                                        break;
                                    case "fileend":
                                        fileend = Utils.HtmlDecode(property.InnerText);
                                        break;
                                    case "line":
                                        line = Utils.HtmlDecode(property.InnerText);
                                        break;
                                }
                            }
                            if(string.IsNullOrEmpty(name) ||
                               string.IsNullOrEmpty(filename) ||
                               string.IsNullOrEmpty(line)) {
                                log(new StatusMessage("Invalid template " + name, StatusMessage.StatusMessageType.Error));
                            } else {
                                cmd.Parameters["@name"].Value = name;
                                cmd.Parameters["@filename"].Value = filename;
                                cmd.Parameters["@filestart"].Value = filestart;
                                cmd.Parameters["@fileend"].Value = fileend;
                                cmd.Parameters["@line"].Value = line;
                                try {
                                    cmd.ExecuteNonQuery();
                                    resTemplates++;
                                } catch(Exception ex) {
                                    if(ex.Message.ToLowerInvariant().Contains("unique")) {
                                        log(new StatusMessage("Template already exists: " + name, StatusMessage.StatusMessageType.Error));
                                    } else {
                                        log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                                    }
                                }
                            }
                        }
                    }
                }
                if(includeUsers) {
                    log(new StatusMessage("Restoring users...", StatusMessage.StatusMessageType.Status));
                    using(Database.Command cmd = db.CreateCommand(trans)) {
                        cmd.CommandText = "INSERT INTO " + db.TablePrefix + "users (username, password, email, permissions, allowedprojects) VALUES (@username, @password, @email, @permissions, @allowedprojects)";
                        cmd.AddParameterWithValue("@username", string.Empty);
                        cmd.AddParameterWithValue("@password", Utils.SaltPassword("password"));
                        cmd.AddParameterWithValue("@email", string.Empty);
                        cmd.AddParameterWithValue("@permissions", string.Empty);
                        cmd.AddParameterWithValue("@allowedprojects", string.Empty);
                        XmlNodeList appsNodes = doc.GetElementsByTagName("user");
                        foreach(XmlNode app in appsNodes) {
                            string username = string.Empty,
                                   email = string.Empty,
                                   permissions = string.Empty,
                                   allowedprojects = string.Empty;
                            foreach(XmlNode property in app) {
                                switch(property.Name.ToLower()) {
                                    case "username":
                                        username = Utils.HtmlDecode(property.InnerText);
                                        break;
                                    case "password":
                                        break;
                                    case "email":
                                        email = Utils.HtmlDecode(property.InnerText);
                                        break;
                                    case "permissions":
                                        permissions = Utils.HtmlDecode(property.InnerText);
                                        break;
                                    case "allowedprojects":
                                        allowedprojects = Utils.HtmlDecode(property.InnerText);
                                        break;
                                }
                            }
                            if(string.IsNullOrEmpty(username) ||
                               string.IsNullOrEmpty(permissions)) {
                                log(new StatusMessage("Invalid user " + username, StatusMessage.StatusMessageType.Error));
                            } else {
                                cmd.Parameters["@username"].Value = username;
                                cmd.Parameters["@email"].Value = email;
                                cmd.Parameters["@permissions"].Value = permissions;
                                cmd.Parameters["@allowedprojects"].Value = allowedprojects;
                                try {
                                    cmd.ExecuteNonQuery();
                                    resUsers++;
                                } catch(Exception ex) {
                                    if(ex.Message.ToLowerInvariant().Contains("unique")) {
                                        log(new StatusMessage("User already exists: " + username, StatusMessage.StatusMessageType.Error));
                                    } else {
                                        log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                                    }
                                }
                            }
                        }
                    }
                }
                //todo: make numbers in the string bold
                log(new StatusMessage(string.Format("\nRestored {0} applications, {1} bundles, {2} elements, {3} templates, {4} resources containing {5} translations", resApps, resBundles, resElements, resTemplates, resResources, resTranslations), StatusMessage.StatusMessageType.Status));
                log(new StatusMessage("Restore complete.", StatusMessage.StatusMessageType.Success));
                trans.Commit();
            }
            db.CloseConnection();
        } catch(Exception ex) {
            log(new StatusMessage("Error restoring #406: " + ex.Message, StatusMessage.StatusMessageType.Error));
        } finally {
            if(db != null) {
                try {
                    db.CloseConnection();
                } catch { }
            }
        }
    }

    /// <summary>
    /// Imports from a ResourceBlender XML spreadsheet.
    /// </summary>
    /// <param name="log">A delegate to which log message should be sent.</param>
    /// <param name="file">The path of the file to import.</param>
    public static void ImportXMLSpreadsheet(StatusMessage.StatusChangedEventHandler log, string file) {
        Database db = null;
        try {
            db = new Database();
            db.OpenConnection();
            ExcelXmlWorkbook wb;
            using(MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(file))) {
                wb = ExcelXmlWorkbook.Import(ms);
            }

            List<string> languagesThisSheet = new List<string>();
            int startingColumn = 1;
            using(DbTransaction trans = db.BeginTransaction()) {
                using(Database.Command cmdCheckExisting = db.CreateCommand(trans))
                using(Database.Command cmdInsertResource = db.CreateCommand(trans))
                using(Database.Command cmdInsertResourceComment = db.CreateCommand(trans))
                using(Database.Command cmdMaxRow = db.CreateCommand(trans)) {
                    cmdCheckExisting.CommandText = "SELECT COUNT(resourceid) FROM " + db.TablePrefix + "resources WHERE resourceid = @tID";
                    cmdCheckExisting.AddParameterWithValue("@tID", "");
                    switch(db.InstanceType) {
                        case Database.DatabaseType.MySQL:
                            cmdInsertResource.CommandText = "INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment) ON DUPLICATE KEY UPDATE translation = @trans, translatorcomment = @comment";
                            cmdInsertResourceComment.CommandText = "INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comm) ON DUPLICATE KEY UPDATE comment = @comm";
                            break;
                        case Database.DatabaseType.SQLServer:
                            cmdInsertResource.CommandText = "UPDATE " + db.TablePrefix + "resources SET translation = @trans, translatorcomment = @comment WHERE resourceid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)";
                            cmdInsertResourceComment.CommandText = "UPDATE " + db.TablePrefix + "resourcecomments SET comment = @comm WHERE resourceid = @tID; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comm)";
                            break;
                        case Database.DatabaseType.SQLite:
                            cmdInsertResource.CommandText = "INSERT OR REPLACE INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)";
                            cmdInsertResourceComment.CommandText = "INSERT OR REPLACE INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comm)";
                            break;
                    }
                    cmdInsertResource.AddParameterWithValue("@tID", "");
                    cmdInsertResource.AddParameterWithValue("@lang", "");
                    cmdInsertResource.AddParameterWithValue("@trans", "");
                    cmdInsertResource.AddParameterWithValue("@comment", "");
                    cmdInsertResourceComment.AddParameterWithValue("@tID", "");
                    cmdInsertResourceComment.AddParameterWithValue("@comm", "");
                    cmdMaxRow.CommandText = "SELECT MAX(resourceid) FROM " + db.TablePrefix + "resources";
                    int maxId = 0;
                    int resourcesUpdated = 0;
                    int j, k;
                    Regex rgxHTMLTags = new Regex("<(.|\n)*?>");
                    for(int i = 0; i < wb.SheetCount; i++) {
                        log(new StatusMessage(string.Format("Restoring from worksheet {0}...", i), StatusMessage.StatusMessageType.Status));
                        Worksheet sheet = wb[i];
                        languagesThisSheet.Clear();
                        for(int colIndex = startingColumn + 1; colIndex < sheet.ColumnCount; colIndex++) { //  first two cells are padding/labels
                            languagesThisSheet.Add(sheet[colIndex, 0].Value.ToString());
                        }
                        int numRows = sheet.RowCount,
                            rowsImported = 0,
                            logIncrements = numRows >= 4 ? numRows / 4 : 1;
                        for(j = 1; j < numRows; j++) {
                            try {
                                Row row = sheet[j];
                                int resourceId = 0;
                                if(!int.TryParse(row[startingColumn].Value.ToString(), out resourceId)) {
                                    using(DbDataReader maxIdReader = cmdMaxRow.ExecuteReader(CommandBehavior.SingleRow)) {
                                        if(maxIdReader.HasRows) {
                                            maxIdReader.Read();
                                            int.TryParse(maxIdReader[0].ToString(), out maxId);
                                            resourceId++;
                                        }
                                        maxIdReader.Close();
                                    }
                                }
                                cmdCheckExisting.Parameters["@tID"].Value = resourceId;
                                for(k = 0; k < row.CellCount - startingColumn - 1; k++) { // add each language to the resources table
                                    Cell currentCell = row[k + startingColumn + 1];
                                    if(k >= languagesThisSheet.Count) {
                                        log(new StatusMessage("Skipping extra columns at row " + j, StatusMessage.StatusMessageType.Status));
                                        continue;
                                    }
                                    string langAbbrev = languagesThisSheet[k];
                                    string resource = currentCell.Value == null ? null : currentCell.Value.ToString();
                                    if(resource != null && resource.Length > 0) {
                                        cmdInsertResource.Parameters["@tID"].Value = resourceId;
                                        cmdInsertResource.Parameters["@lang"].Value = langAbbrev;
                                        cmdInsertResource.Parameters["@trans"].Value = rgxHTMLTags.Replace(resource, string.Empty).Replace("&gt;", ">").Replace("&lt;", "<");
                                        if(!string.IsNullOrEmpty(currentCell.Comment)) {
                                            cmdInsertResource.Parameters["@comment"].Value = rgxHTMLTags.Replace(currentCell.Comment, string.Empty).Replace("&gt;", ">").Replace("&lt;", "<");
                                        } else {
                                            cmdInsertResource.Parameters["@comment"].Value = DBNull.Value;
                                        }
                                        cmdInsertResource.ExecuteNonQuery();
                                        resourcesUpdated++;
                                    }
                                }
                                string comment = row[startingColumn].Comment;
                                if(comment != null && comment.Length > 0) {
                                    cmdInsertResourceComment.Parameters["@tID"].Value = resourceId;
                                    cmdInsertResourceComment.Parameters["@comm"].Value = rgxHTMLTags.Replace(comment, string.Empty).Replace("&gt;", ">").Replace("&lt;", "<"); ;
                                    cmdInsertResourceComment.ExecuteNonQuery();
                                }
                            } catch {
                                log(new StatusMessage("Error reading row " + j, StatusMessage.StatusMessageType.Error));
                            }
                            // TODO: add new resource by appending rows to spreadsheet (read max id etc.)
                            rowsImported++;
                            if(rowsImported % logIncrements == 0) {
                                log(new StatusMessage(string.Format("Imported {0}/{1} rows", rowsImported, numRows), StatusMessage.StatusMessageType.Success));
                            }
                        }
                    }
                    log(new StatusMessage(string.Format("Added {0} elements", resourcesUpdated), StatusMessage.StatusMessageType.Success));
                }
                trans.Commit();
            }
        } catch(Exception ex) {
            log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
        } finally {
            if(db != null) {
                try {
                    db.CloseConnection();
                } catch { }
            }
        }
    }

    /// <summary>
    /// Imports from an RBT translation database.
    /// </summary>
    /// <param name="log">The log handler.</param>
    /// <param name="fileName">The path of the file to import.</param>
    /// <param name="selectedLanguages">The codes of the languages to import.</param>
    public static void ImportRBT(StatusMessage.StatusChangedEventHandler log, string fileName, Dictionary<string, CultureInfo> selectedLanguages) {
        throw new ResourceBlenderException("Until SQLite works on medium trust, RBT import/export will be disabled. Please Use ResourceBlender Express to import or export RBT translation databases.");
        /*Database db = null;
        SQLiteConnection dbSqlite = null;
        int numUpdated = 0,
            numDeleted = 0;
        try {
            dbSqlite = new SQLiteConnection(Database.GetSQLiteConnectionString(fileName));
            dbSqlite.Open();
            using(SQLiteCommand cmdSqlite = dbSqlite.CreateCommand()) {
                db = new Database();
                db.OpenConnection();
                cmdSqlite.CommandText = "SELECT resourceid, language, translation, translatorcomment FROM resources WHERE " + db.GenerateInClause("language", selectedLanguages.Values.Select(info => info.Name).ToArray(), false);
                using(SQLiteDataReader readerSqlite = cmdSqlite.ExecuteReader()) {
                    if(!readerSqlite.HasRows) {
                        throw new SQLiteException("The file does not contain any translations into the target language");
                    }
                    using(DbTransaction trans = db.BeginTransaction()) {
                        using(Database.Command cmdInsert = db.CreateCommand(trans)) // for inserting/updating translations
                        using(Database.Command cmdDelete = db.CreateCommand(trans)) { // for deleting empty translations
                            switch(db.InstanceType) {
                                case Database.DatabaseType.MySQL:
                                    cmdInsert.CommandText = string.Format("INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@rID, @lang, @trans, @comment) ON DUPLICATE KEY UPDATE translation = @trans, translatorcomment = @comment", db.TablePrefix);
                                    break;
                                case Database.DatabaseType.SQLServer:
                                    cmdInsert.CommandText = string.Format("UPDATE {0}resources SET translation = @trans, translatorcomment = @comment WHERE resourceid = @rID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@rID, @lang, @trans, @comment)", db.TablePrefix);
                                    break;
                                case Database.DatabaseType.SQLite:
                                    cmdInsert.CommandText = string.Format("INSERT OR REPLACE INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@rID, @lang, @trans, @comment)", db.TablePrefix);
                                    break;
                            }
                            cmdInsert.AddParameterWithValue("@rID", "");
                            cmdInsert.AddParameterWithValue("@lang", "");
                            cmdInsert.AddParameterWithValue("@trans", "");
                            cmdInsert.AddParameterWithValue("@comment", "");
                            cmdDelete.CommandText = string.Format("DELETE FROM {0}resources WHERE resourceid = @rID AND language = @lang", db.TablePrefix);
                            cmdDelete.AddParameterWithValue("@rID", "");
                            cmdDelete.AddParameterWithValue("@lang", "");
                            while(readerSqlite.Read()) {
                                try {
                                    string resourceId = readerSqlite["resourceid"].ToString(),
                                           language = readerSqlite["language"].ToString(),
                                           translation = readerSqlite["translation"].ToString(),
                                           translatorComment = readerSqlite["translatorcomment"].ToString();
                                    if(translation.Length == 0) { // delete the translation
                                        cmdDelete.Parameters["@rID"].Value = resourceId;
                                        cmdDelete.Parameters["@lang"].Value = language;
                                        int affected = cmdDelete.ExecuteNonQuery();
                                        if(affected > 0) {
                                            numDeleted += affected;
                                            log(new StatusMessage(string.Format("Deleted {0} translation for resource #{1}", selectedLanguages[language].EnglishName, resourceId), StatusMessage.StatusMessageType.Success));
                                        }
                                    } else {
                                        cmdInsert.Parameters["@rID"].Value = resourceId;
                                        cmdInsert.Parameters["@lang"].Value = language;
                                        cmdInsert.Parameters["@trans"].Value = translation;
                                        if(translatorComment.Length == 0) {
                                            cmdInsert.Parameters["@comment"].Value = DBNull.Value;
                                        } else {
                                            cmdInsert.Parameters["@comment"].Value = translatorComment;
                                        }
                                        int affected = cmdInsert.ExecuteNonQuery();
                                        if(affected > 0) {
                                            numUpdated += affected;
                                            log(new StatusMessage(string.Format("Updated {0} translation for resource #{1}", selectedLanguages[language].EnglishName, resourceId), StatusMessage.StatusMessageType.Success));
                                        }
                                    }
                                } catch(Exception ex) {
                                    log(new StatusMessage(string.Format("Error processing translation: {0}", ex.Message), StatusMessage.StatusMessageType.Error));
                                }
                            }
                        }
                        trans.Commit();
                    }
                }
            }
        } catch(Exception ex) {
            log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
        } finally {
            if(db != null) {
                try {
                    db.CloseConnection();
                } catch { }
            }
            if(dbSqlite != null) {
                try {
                    dbSqlite.Close();
                } catch { }
            }
            log(new StatusMessage(string.Format("Updated {0} {1}, deleted {2} {3}", numUpdated, numUpdated == 1 ? "translation" : "translations", numDeleted, numDeleted == 1 ? "translation" : "translations"), StatusMessage.StatusMessageType.Success));
        }*/
    }

    /// <summary>
    /// Imports elements and resources from existing .properties bundles, then prompts for an application to which to import.
    /// </summary>
    /// <param name="log">A delegate to which log message should be sent.</param>
    /// <param name="files">The files to import.</param>
    /// <param name="application">The application into which to import the bundles.</param>
    public static void ImportProperties(StatusMessage.StatusChangedEventHandler log, Dictionary<string, string> files, RBApplication application) {
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> bundles = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <bundle, <elementname<language, resource>>>
        string bundleName, language, elementName, resource;
        int splitter, elementSplitter;
        Dictionary<string, Dictionary<string, string>> currentBundle;
        log(new StatusMessage("Parsing bundles...", StatusMessage.StatusMessageType.Status));
        foreach(KeyValuePair<string, string> file in files) { // <filename, UTF8 contents>
            string fileNameNoExt = Path.GetFileNameWithoutExtension(file.Key);
            splitter = fileNameNoExt.IndexOf('_');
            if(splitter <= 0) {
                continue;
            }
            bundleName = fileNameNoExt.Substring(0, splitter);
            language = Utils.JavaCulture2DotNetCulture(fileNameNoExt.Substring(splitter + 1));
            if(bundles.ContainsKey(bundleName)) {
                currentBundle = bundles[bundleName];
            } else {
                currentBundle = new Dictionary<string, Dictionary<string, string>>();
                bundles.Add(bundleName, currentBundle);
            }
            string[] elements = file.Value.Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries);
            foreach(string totalElement in elements) {
                try {
                    elementSplitter = totalElement.IndexOf('=');
                    elementName = totalElement.Substring(0, elementSplitter);
                    resource = rx.Replace(totalElement.Substring(elementSplitter + 1), match => ((char)Int32.Parse(match.Value.Substring(2), NumberStyles.HexNumber)).ToString()).TrimEnd('\r');
                    if(currentBundle.ContainsKey(elementName)) {
                        currentBundle[elementName].Add(language, resource);
                    } else {
                        Dictionary<string, string> element = new Dictionary<string, string>();
                        element.Add(language, resource);
                        currentBundle.Add(elementName, element);
                    }
                } catch(Exception ex) {
                    log(new StatusMessage(string.Format("Failed to parse element '{0}': {1}", totalElement, ex.Message), StatusMessage.StatusMessageType.Error));
                }
            }
        }
        ProcessBundles(bundles, application, log);
    }

    /// <summary>
    /// Imports from .NET managed resource (.resx) files.
    /// </summary>
    /// <param name="log">A delegate to which log message should be sent.</param>
    /// <param name="files">The files to import.</param>
    /// <param name="application">The application into which to import the bundles.</param>
    public static void ImportResx(StatusMessage.StatusChangedEventHandler log, Dictionary<string, string> files, RBApplication application) {
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> bundles = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <bundle, <elementname<language, resource>>>
        string bundleName, language, elementName, resource, dataType;
        XmlAttributeCollection attr;
        int splitter;
        Dictionary<string, Dictionary<string, string>> currentBundle;
        log(new StatusMessage("Parsing bundles...", StatusMessage.StatusMessageType.Status));
        foreach(KeyValuePair<string, string> file in files) { // <filename, UTF8 contents>
            string fileNameNoExt = Path.GetFileNameWithoutExtension(file.Key);
            splitter = fileNameNoExt.IndexOf('.');
            if(splitter <= 0) {
                continue;
            }
            bundleName = fileNameNoExt.Substring(0, splitter);
            language = fileNameNoExt.Substring(splitter + 1).Replace('.', '-');
            switch(language) { // the GetCultures() method lists them as this, so if they're imported as Hans and Hant, they don't match up.
                case "zh-Hans":
                    language = "zh-CHS";
                    break;
                case "zh-Hant":
                    language = "zh-CHT";
                    break;
            }
            if(bundles.ContainsKey(bundleName)) {
                currentBundle = bundles[bundleName];
            } else {
                currentBundle = new Dictionary<string, Dictionary<string, string>>();
                bundles.Add(bundleName, currentBundle);
            }
            XmlDocument doc = new XmlDocument();
            try {
                doc.LoadXml(file.Value);
            } catch(Exception ex) {
                log(new StatusMessage("Invalid XML file: " + file.Key + " - " + ex.Message, StatusMessage.StatusMessageType.Error));
                continue;
            }
            if(doc.ChildNodes.Count == 0) {
                log(new StatusMessage("Invalid resx file: " + file.Key, StatusMessage.StatusMessageType.Error));
                continue;
            }
            XmlNodeList nodes = doc.SelectNodes("/root/data[count(value)=1]");
            foreach(XmlNode node in nodes) {
                if(node.NodeType == XmlNodeType.Element) {
                    dataType = null;
                    attr = node.Attributes;
                    if(attr["type"] != null) {
                        dataType = attr["type"].Value;
                    } else if(attr["mimetype"] != null) {
                        dataType = attr["mimetype"].Value;
                    }
                    if(attr["name"] == null) {
                        log(new StatusMessage(string.Format("Invalid element in file {0}: name attribute missing", file.Key), StatusMessage.StatusMessageType.Error));
                        continue;
                    } else {
                        elementName = attr["name"].Value;
                    }
                    resource = string.Empty;
                    foreach(XmlNode child in node) {
                        if(child.NodeType == XmlNodeType.Element) {
                            if(child.Name == "value") {
                                resource = child.InnerText;
                            }
                        }
                    }
                    switch(dataType) {
                        case null: // not set, resource is a string
                            if(currentBundle.ContainsKey(elementName)) {
                                if(!currentBundle[elementName].ContainsKey(language)) {
                                    currentBundle[elementName].Add(language, resource);
                                }
                            } else {
                                Dictionary<string, string> element = new Dictionary<string, string>();
                                element.Add(language, resource);
                                currentBundle.Add(elementName, element);
                            }
                            break;
                    }
                }
            }
        }
        ProcessBundles(bundles, application, log);
    }

    /// <summary>
    /// Inserts bundles, elements and translations into the database.
    /// </summary>
    /// <param name="bundles">The bundles to import.</param>
    /// <param name="application">The application into which to import the bundles.</param>
    /// <param name="log">A delegate to which log message should be sent.</param>
    public static void ProcessBundles(Dictionary<string, Dictionary<string, Dictionary<string, string>>> bundles, RBApplication application, StatusMessage.StatusChangedEventHandler log) {
        log(new StatusMessage("Finished parsing, starting import...", StatusMessage.StatusMessageType.Success));
        Database db = null;
        try {
            db = new Database();
            db.OpenConnection();
            int numBundles = 0,
                numElements = 0,
                numResources = 0;
            using(DbTransaction trans = db.BeginTransaction()) {
                using(Database.Command cmd = db.CreateCommand(trans))
                using(Database.Command cmdInsertBundle = db.CreateCommand(trans))
                using(Database.Command cmdCheckExisting = db.CreateCommand(trans))
                using(Database.Command cmdInsertElement = db.CreateCommand(trans))
                using(Database.Command cmdFindSimilar = db.CreateCommand(trans))
                using(Database.Command cmdInsertResource = db.CreateCommand(trans))
                using(Database.Command cmdMaxRow = db.CreateCommand(trans)) {
                    Hashtable appBundles = new Hashtable(); // <bundlename, bundleid>
                    cmd.CommandText = "SELECT id, name FROM " + db.TablePrefix + "bundles WHERE application = @id";
                    cmd.AddParameterWithValue("@id", application.ID);
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        appBundles.Add(reader[1], reader[0]);
                    }
                    reader.Close();

                    cmdInsertBundle.CommandText = "INSERT INTO " + db.TablePrefix + "bundles (id, name, application) VALUES (@id, @name, @application)";
                    cmdInsertBundle.AddParameterWithValue("@id", "");
                    cmdInsertBundle.AddParameterWithValue("@name", "");
                    cmdInsertBundle.AddParameterWithValue("@application", application.ID);

                    cmdCheckExisting.CommandText = "SELECT resourceid FROM " + db.TablePrefix + "elements WHERE applicationid = @appId AND bundle = @bundle AND elementname = @element";
                    cmdCheckExisting.AddParameterWithValue("@appId", application.ID);
                    cmdCheckExisting.AddParameterWithValue("@bundle", "");
                    cmdCheckExisting.AddParameterWithValue("@element", "");

                    cmdInsertElement.CommandText = "INSERT INTO " + db.TablePrefix + "elements (applicationid, bundle, elementname, resourceid) VALUES (@appId, @bundle, @element, @tId)";
                    cmdInsertElement.AddParameterWithValue("@appId", application.ID);
                    cmdInsertElement.AddParameterWithValue("@bundle", "");
                    cmdInsertElement.AddParameterWithValue("@element", "");
                    cmdInsertElement.AddParameterWithValue("@tId", "");

                    cmdFindSimilar.CommandText = "SELECT resourceid FROM " + db.TablePrefix + "resources WHERE language = @language AND translation = @translation";
                    cmdFindSimilar.AddParameterWithValue("@language", application.DefaultLanguage); // Only match if the default language for this app. is present in the uploaded resources
                    cmdFindSimilar.AddParameterWithValue("@translation", "");

                    switch(db.InstanceType) {
                        case Database.DatabaseType.MySQL:
                            cmdInsertResource.CommandText = "INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation) VALUES (@tId, @language, @translation) ON DUPLICATE KEY UPDATE translation = @translation";
                            break;
                        case Database.DatabaseType.SQLServer:
                            cmdInsertResource.CommandText = "UPDATE " + db.TablePrefix + "resources SET translation = @translation WHERE resourceid = @tId AND language = @language; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation) VALUES (@tId, @language, @translation)";
                            break;
                        case Database.DatabaseType.SQLite:
                            cmdInsertResource.CommandText = "INSERT OR REPLACE INTO " + db.TablePrefix + "resources (resourceid, language, translation) VALUES (@tId, @language, @translation)";
                            break;
                    }
                    cmdInsertResource.AddParameterWithValue("@tID", "");
                    cmdInsertResource.AddParameterWithValue("@language", "");
                    cmdInsertResource.AddParameterWithValue("@translation", "");

                    cmdMaxRow.CommandText = "SELECT MAX(resourceid) FROM " + db.TablePrefix + "resources";

                    int resourceId = 0;
                    string bundleId;
                    foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> bundle in bundles) { // <bundle, <element, <language, translation>>>
                        log(new StatusMessage(string.Format("Importing bundle {0}...", bundle.Key), StatusMessage.StatusMessageType.Status));
                        numBundles++;
                        if(appBundles.ContainsKey(bundle.Key)) {
                            bundleId = appBundles[bundle.Key].ToString();
                        } else {
                            bundleId = Utils.GenID(true);
                            cmdInsertBundle.Parameters["@id"].Value = bundleId;
                            cmdInsertBundle.Parameters["@name"].Value = bundle.Key;
                            cmdInsertBundle.ExecuteNonQuery();
                            appBundles.Add(bundle.Key, bundleId);
                        }
                        cmdCheckExisting.Parameters["@bundle"].Value = bundleId;
                        cmdInsertElement.Parameters["@bundle"].Value = bundleId;
                        int logIncrements = bundle.Value.Count >= 4 ? bundle.Value.Count / 4 : 1;
                        int elementsThisBundle = 0;
                        foreach(KeyValuePair<string, Dictionary<string, string>> element in bundle.Value) { // <element, <language, translation>>
                            elementsThisBundle++;
                            cmdCheckExisting.Parameters["@element"].Value = element.Key;
                            reader = cmdCheckExisting.ExecuteReader();
                            if(reader.HasRows) { // the element already exists, overwrite existing languages with the ones from the bundle
                                reader.Read();
                                int.TryParse(reader[0].ToString(), out resourceId);
                                reader.Close();
                            } else { // no matching elements, create one first
                                reader.Close();
                                cmdInsertElement.Parameters["@element"].Value = element.Key;

                                // check for other resources which might be have the same translation, then append the translations in this bundle to them instead of creating a duplicate resource. 
                                bool readId = false;
                                if(element.Value.ContainsKey(application.DefaultLanguage)) {
                                    cmdFindSimilar.Parameters["@translation"].Value = element.Value[application.DefaultLanguage];
                                    reader = cmdFindSimilar.ExecuteReader();
                                    if(reader.HasRows) { // resource of current element matches one already in the database, use the existing resource ID
                                        reader.Read();
                                        resourceId = reader.GetInt32(0);
                                        readId = true;
                                    }
                                    reader.Close();
                                }
                                if(!readId) { // no matching resources, find the next available resource ID
                                    using(DbDataReader maxIdReader = cmdMaxRow.ExecuteReader(CommandBehavior.SingleRow)) {
                                        if(maxIdReader.HasRows) {
                                            maxIdReader.Read();
                                            int.TryParse(maxIdReader[0].ToString(), out resourceId);
                                            resourceId++;
                                        }
                                        maxIdReader.Close();
                                    }
                                }
                                cmdInsertElement.Parameters["@tId"].Value = resourceId;
                                cmdInsertElement.ExecuteNonQuery();
                            }
                            cmdInsertResource.Parameters["@tId"].Value = resourceId;
                            foreach(KeyValuePair<string, string> resource in element.Value) { // <language, translation>>
                                numResources++;
                                cmdInsertResource.Parameters["@language"].Value = resource.Key;
                                cmdInsertResource.Parameters["@translation"].Value = resource.Value;
                                try {
                                    cmdInsertResource.ExecuteNonQuery();
                                } catch(Exception ex) {
                                    log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                                }
                            }
                            if(logIncrements > 0 && elementsThisBundle % logIncrements == 0) {
                                log(new StatusMessage(string.Format("Imported {0}/{1} elements", elementsThisBundle, bundle.Value.Count), StatusMessage.StatusMessageType.Success));
                            }
                        }
                        numElements += elementsThisBundle;
                    }
                }
                trans.Commit();
                log(new StatusMessage(string.Format("Imported {0} resources from {1} bundles containing {2} elements.", numResources, numBundles, numElements), StatusMessage.StatusMessageType.Success));
            }
        } catch(Exception ex) {
            log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
        } finally {
            if(db != null) {
                try {
                    db.CloseConnection();
                } catch { }
            }
        }
    }

    /// <summary>
    /// Imports from GNU PO files.
    /// </summary>
    /// <param name="files">The files to import.</param>
    /// <param name="logHandler">The log handler.</param>
    public static void ImportPO(StatusMessage.StatusChangedEventHandler log, Dictionary<string, string> files, RBApplication application) {
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> bundles = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <BundleName, <msgId<language, translation>>>
        Dictionary<string, Dictionary<string, POMetaData>> elementData = new Dictionary<string, Dictionary<string, POMetaData>>(); // <BundleName, <ElementName, Meta-data>>
        string bundleName, language;
        int splitter;
        log(new StatusMessage("Parsing bundles...", StatusMessage.StatusMessageType.Status));
        Regex rgxUntranslatedString = new Regex("msgid \"(.*)\""),
              rgxTranslatedString = new Regex("msgstr \"(.*)\""),
              rgxUntranslatedPlural = new Regex("msgid_plural \"(.*)\""),
              rgxTranslatedPlural = new Regex("msgstr\\[(\\d)\\] \"(.*)\""),
              rgxContext = new Regex("msgctxt \".*\"");
        foreach(KeyValuePair<string, string> file in files) { // <filename, UTF8 contents>
            if(file.Value.Length == 0) {
                continue;
            }
            string fileNameNoExt = Path.GetFileNameWithoutExtension(file.Key);
            splitter = file.Key.IndexOf('/');
            if(splitter <= 0) { // no directory means no bundle, create a new bundle
                //todo: 1 - check this works with subdirs
                bundleName = DateTime.Now.ToString("HH:mm") + "-" + new Random().Next(100);
            } else {
                bundleName = file.Key.Substring(0, splitter);
            }
            language = fileNameNoExt;
            if(!bundles.ContainsKey(bundleName)) {
                bundles.Add(bundleName, new Dictionary<string, Dictionary<string, string>>());
                elementData.Add(bundleName, new Dictionary<string, POMetaData>());
            }
            string[] entries = file.Value.Split(new string[] { "\n\n" }, StringSplitOptions.RemoveEmptyEntries);
            foreach(string entry in entries) {
                string[] lines = entry.Split('\n');
                try {
                    POMetaData data = new POMetaData();
                    string line;
                    StringBuilder translatorComments = null;
                    for(int i = 0; i < lines.Length; i++) {
                        line = lines[i].Trim();
                        if(line.Length > 4) {
                            switch(line.Substring(0, 2)) {
                                case "# ":
                                    if(translatorComments == null) {
                                        translatorComments = new StringBuilder();
                                    }
                                    translatorComments.AppendLine(line.Substring(2).Trim());
                                    break;
                                case "#.":
                                    data.ExtractedComments.Add(line.Substring(2).Trim());
                                    break;
                                case "#:":
                                    data.References.Add(line.Substring(2).Trim());
                                    break;
                                case "#,":
                                    data.Flags.Add(line.Substring(2).Trim());
                                    break;
                                default:
                                    if(line.Length > 7) { // min is 'msgid ""'
                                        int j;
                                        if(line.StartsWith("msgid") && line.Length >= 8) {
                                            Match matchUntranslated = rgxUntranslatedString.Match(line);
                                            if(matchUntranslated.Success && matchUntranslated.Groups[1].Value.Length == 0) { // line is msgid "" -  multiline string, move to the next line to start reading
                                                StringBuilder multiLine = new StringBuilder();
                                                for(j = i + 1; j < lines.Length; j++) {
                                                    string nextLine = lines[j].Trim();
                                                    if(nextLine.Length < 2 || nextLine[0] != '"') { // either an unexpected char or anything but a quote means the end of the multi-line string
                                                        break;
                                                    } else if(line.Length > 3) { // A line could contain just "", but it should be skipped if so
                                                        multiLine.Append(nextLine.Substring(1, nextLine.Length - 2));
                                                    }
                                                }
                                                if(multiLine.Length > 0) {
                                                    data.UntranslatedString = multiLine.ToString();
                                                    i = j - 1;
                                                }
                                            } else if(line.Length >= 9) { // Must be at least msgid "x"
                                                Match matchPluralUntranslated = rgxUntranslatedPlural.Match(line);
                                                if(matchPluralUntranslated.Success) {
                                                    data.UntranslatedPlural = matchPluralUntranslated.Groups[1].Value;
                                                } else {
                                                    data.UntranslatedString = matchUntranslated.Groups[1].Value;
                                                }
                                            }
                                        } else if(line.StartsWith("msgstr") && line.Length >= 9) {
                                            Match matchTranslated = rgxTranslatedString.Match(line);
                                            if(matchTranslated.Success && matchTranslated.Groups[1].Length == 0) { // line is msgstr "" -  multiline string, move to the next line to start reading
                                                StringBuilder multiLine = new StringBuilder();
                                                for(j = i + 1; j < lines.Length; j++) {
                                                    string nextLine = lines[j].Trim();
                                                    if(nextLine.Length < 2 || nextLine[0] != '"') { // either an unexpected char or anything but a quote means the end of the multi-line string
                                                        break;
                                                    } else if(line.Length > 3) { // A line could contain just "", but it should be skipped if so
                                                        multiLine.Append(nextLine.Substring(1, nextLine.Length - 2));
                                                    }
                                                }
                                                if(multiLine.Length > 0) {
                                                    data.TranslatedString = multiLine.ToString();
                                                    i = j - 1;
                                                }
                                            } else if(line.Length >= 10) { // Must be at least msgstr "x"
                                                Match matchTranslatedPlural = rgxTranslatedPlural.Match(line);
                                                if(matchTranslatedPlural.Success) {
                                                    if(data.TranslatedPlurals == null) {
                                                        data.TranslatedPlurals = new Dictionary<int, Dictionary<string, string>>();
                                                    }
                                                    data.TranslatedPlurals.Add(int.Parse(matchTranslatedPlural.Groups[1].Value), new Dictionary<string, string>() { { language, matchTranslatedPlural.Groups[2].Value } });
                                                } else {
                                                    data.TranslatedString = matchTranslated.Groups[1].Value;
                                                }
                                            }
                                        } else if(line.StartsWith("msgctxt") && line.Length >= 10) {
                                            Match matchContext = rgxContext.Match(line);
                                            data.Context = matchContext.Groups[1].Value;
                                        }
                                    }
                                    break;
                            }
                        }
                    }
                    if(translatorComments != null) {
                        data.TranslatorsComments.Add(language, translatorComments.ToString());
                    }
                    string msgIdHash = Utils.MD5(data.UntranslatedString);
                    if(data.TranslatedPlurals != null) {
                        data.TranslatedString = data.TranslatedPlurals[0][language].ToString(); // the singular form
                    }
                    if(!bundles[bundleName].ContainsKey(msgIdHash)) { // first entry in  this bundle
                        bundles[bundleName].Add(msgIdHash, new Dictionary<string, string>());
                        elementData[bundleName].Add(msgIdHash, data);
                    } else { // the meta data has already been added, append the plural and comment meta data from this entry to the existing data
                        if(data.TranslatedPlurals != null) {
                            foreach(KeyValuePair<int, Dictionary<string, string>> plural in data.TranslatedPlurals) {
                                if(!elementData[bundleName][msgIdHash].TranslatedPlurals.ContainsKey(plural.Key)) {
                                    elementData[bundleName][msgIdHash].TranslatedPlurals.Add(plural.Key, new Dictionary<string, string>());
                                }
                                elementData[bundleName][msgIdHash].TranslatedPlurals[plural.Key].Add(language, plural.Value[language]);
                            }
                        }
                        if(data.TranslatorsComments.Count > 0) {
                            foreach(KeyValuePair<string, string> comment in data.TranslatorsComments) {
                                if(!elementData[bundleName][msgIdHash].TranslatorsComments.ContainsKey(comment.Key)) { // no translator comments in this language
                                    elementData[bundleName][msgIdHash].TranslatorsComments.Add(comment.Key, comment.Value);
                                } else {
                                    elementData[bundleName][msgIdHash].TranslatorsComments[comment.Key] = comment.Value;
                                }
                            }
                        }
                    }
                    if(data.TranslatedString.Length > 0 && !bundles[bundleName][msgIdHash].ContainsKey(language)) {
                        bundles[bundleName][msgIdHash].Add(language, data.TranslatedString);
                    }
                } catch(Exception ex) {
                    log(new StatusMessage(string.Format("Failed to parse entry  '{0}': {1}", entry, ex.Message), StatusMessage.StatusMessageType.Error));
                }
            }
        }

        log(new StatusMessage("Finished parsing, starting import", StatusMessage.StatusMessageType.Success));

        Database db = null;
        try {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(DbTransaction trans = db.BeginTransaction()) {
                int numBundles = 0,
                    numElements = 0,
                    numResources = 0;
                using(Database.Command cmd = db.CreateCommand(trans))
                using(Database.Command cmdInsertBundle = db.CreateCommand(trans))
                using(Database.Command cmdCheckExisting = db.CreateCommand(trans))
                using(Database.Command cmdInsertElement = db.CreateCommand(trans))
                using(Database.Command cmdUpdateElement = db.CreateCommand(trans))
                using(Database.Command cmdFindSimilar = db.CreateCommand(trans))
                using(Database.Command cmdInsertResource = db.CreateCommand(trans))
                using(Database.Command cmdMaxRow = db.CreateCommand(trans)) {
                    Hashtable appBundles = new Hashtable(); // <bundlename, bundleid>
                    cmd.CommandText = "SELECT name, id FROM " + db.TablePrefix + "bundles WHERE application = @id";
                    cmd.AddParameterWithValue("@id", application.ID);
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        appBundles.Add(reader[0], reader[1]);
                    }
                    reader.Close();

                    cmdInsertBundle.CommandText = "INSERT INTO " + db.TablePrefix + "bundles (id, name, application) VALUES (@id, @name, @application)";
                    cmdInsertBundle.AddParameterWithValue("@id", "");
                    cmdInsertBundle.AddParameterWithValue("@name", "");
                    cmdInsertBundle.AddParameterWithValue("@application", application.ID);

                    cmdCheckExisting.CommandText = "SELECT resourceid FROM " + db.TablePrefix + "elements WHERE applicationid = @appId AND bundle = @bundle AND elementname = @element";
                    cmdCheckExisting.AddParameterWithValue("@appId", application.ID);
                    cmdCheckExisting.AddParameterWithValue("@bundle", "");
                    cmdCheckExisting.AddParameterWithValue("@element", "");

                    cmdInsertElement.CommandText = "INSERT INTO " + db.TablePrefix + "elements (applicationid, bundle, elementname, resourceid, comment) VALUES (@appId, @bundle, @element, @tId, @metaData)";
                    cmdInsertElement.AddParameterWithValue("@appId", application.ID);
                    cmdInsertElement.AddParameterWithValue("@bundle", "");
                    cmdInsertElement.AddParameterWithValue("@element", "");
                    cmdInsertElement.AddParameterWithValue("@tId", "");
                    cmdInsertElement.AddParameterWithValue("@metaData", "");

                    cmdUpdateElement.CommandText = "UPDATE " + db.TablePrefix + "elements SET comment = @metaData WHERE applicationid = @appId AND bundle = @bundle AND elementname = @element";
                    cmdUpdateElement.AddParameterWithValue("@appId", application.ID);
                    cmdUpdateElement.AddParameterWithValue("@bundle", "");
                    cmdUpdateElement.AddParameterWithValue("@element", "");
                    cmdUpdateElement.AddParameterWithValue("@metaData", "");

                    cmdFindSimilar.CommandText = "SELECT resourceid FROM " + db.TablePrefix + "resources WHERE language = @language AND translation = @translation";
                    cmdFindSimilar.AddParameterWithValue("@language", application.DefaultLanguage); // Only match if the default language for this app. is present in the uploaded resources
                    cmdFindSimilar.AddParameterWithValue("@translation", "");

                    switch(db.InstanceType) {
                        case Database.DatabaseType.MySQL:
                            cmdInsertResource.CommandText = "INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tId, @language, @translation, @comment) ON DUPLICATE KEY UPDATE translation = @translation, translatorcomment = @comment";
                            break;
                        case Database.DatabaseType.SQLServer:
                            cmdInsertResource.CommandText = "UPDATE " + db.TablePrefix + "resources SET translation = @translation, translatorcomment = @comment WHERE resourceid = @tId AND language = @language; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tId, @language, @translation, @comment)";
                            break;
                        case Database.DatabaseType.SQLite:
                            cmdInsertResource.CommandText = "INSERT OR REPLACE INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tId, @language, @translation, @comment)";
                            break;
                    }
                    cmdInsertResource.AddParameterWithValue("@tID", "");
                    cmdInsertResource.AddParameterWithValue("@language", "");
                    cmdInsertResource.AddParameterWithValue("@translation", "");
                    cmdInsertResource.AddParameterWithValue("@comment", "");

                    cmdMaxRow.CommandText = "SELECT MAX(resourceid) FROM " + db.TablePrefix + "resources";

                    int resourceId = 0;
                    string bundleId;
                    foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> bundle in bundles) { // <bundle, <msgId, <language, translation>>>
                        log(new StatusMessage(string.Format("Importing bundle {0}...", bundle.Key), StatusMessage.StatusMessageType.Status));
                        bundleName = bundle.Key;
                        if(appBundles.ContainsKey(bundleName)) {
                            bundleId = appBundles[bundleName].ToString();
                        } else {
                            bundleId = Utils.GenID(true);
                            cmdInsertBundle.Parameters["@id"].Value = bundleId;
                            cmdInsertBundle.Parameters["@name"].Value = bundle.Key;
                            cmdInsertBundle.ExecuteNonQuery();
                            appBundles.Add(bundleName, bundleId);
                        }
                        cmdCheckExisting.Parameters["@bundle"].Value = bundleId;
                        cmdInsertElement.Parameters["@bundle"].Value = bundleId;
                        string elementName; // the first reference for the entry
                        POMetaData thisElementData;
                        foreach(KeyValuePair<string, Dictionary<string, string>> entry in bundle.Value) { // <msgId, <language, translation>>
                            numElements++;
                            thisElementData = elementData[bundleName][entry.Key];
                            string elementContext = thisElementData.Context == null ? "" : "-" + thisElementData.Context;
                            string msgIdSnippet;
                            if(thisElementData.UntranslatedString == "") {
                                elementName = "Generator";
                            } else {
                                if(thisElementData.UntranslatedString.Length > 0) {
                                    msgIdSnippet = thisElementData.UntranslatedString.Substring(0, thisElementData.UntranslatedString.Length > 10 ? 10 : thisElementData.UntranslatedString.Length); // truncate to 10 chars
                                } else if(thisElementData.TranslatedString.Length > 0) {
                                    msgIdSnippet = thisElementData.TranslatedString.Substring(0, thisElementData.TranslatedString.Length > 10 ? 10 : thisElementData.TranslatedString.Length); // truncate to 10 chars
                                } else {
                                    msgIdSnippet = "Unnamed " + entry.Key.Substring(0, 10);
                                }
                                if(thisElementData.References.Count > 0) {
                                    string elementReference = elementData[bundleName][entry.Key].References[0]; // use the first reference to generate part of the element name
                                    int slashPos = elementReference.LastIndexOf('/'),
                                        colonPos = elementReference.LastIndexOf(':');
                                    if(slashPos >= 0) { // directory included
                                        if(colonPos > 1) {
                                            elementName = (elementReference.Substring(slashPos + 1, colonPos - slashPos - 1) + thisElementData.Context + "-" + msgIdSnippet + "-" + entry.Key.Substring(0, 4)).Replace(" ", "");
                                        } else {
                                            elementName = (elementReference.Substring(slashPos + 1) + thisElementData.Context + "-" + msgIdSnippet + "-" + entry.Key.Substring(0, 4)).Replace(" ", "");
                                        }
                                    } else { // no directory
                                        if(colonPos > 1) {
                                            elementName = (elementReference.Substring(0, colonPos) + thisElementData.Context + "-" + msgIdSnippet + "-" + entry.Key.Substring(0, 4)).Replace(" ", "");
                                        } else {
                                            elementName = (elementReference + thisElementData.Context + "-" + msgIdSnippet + "-" + entry.Key.Substring(0, 4)).Replace(" ", "");
                                        }
                                    }
                                } else {
                                    elementName = (thisElementData.Context + "-" + msgIdSnippet + "-" + entry.Key.Substring(0, 4)).Replace(" ", "");
                                }
                            }
                            cmdCheckExisting.Parameters["@element"].Value = elementName;
                            reader = cmdCheckExisting.ExecuteReader();
                            if(reader.HasRows) { // the element already exists, overwrite existing languages with the ones from the bundle
                                reader.Read();
                                int.TryParse(reader[0].ToString(), out resourceId);
                                reader.Close();
                                cmdUpdateElement.Parameters["@metaData"].Value = elementData[bundleName][entry.Key].ToCommentsField();
                            } else { // no matching elements, create one first
                                reader.Close();
                                cmdInsertElement.Parameters["@element"].Value = elementName;
                                cmdInsertElement.Parameters["@metaData"].Value = elementData[bundleName][entry.Key].ToCommentsField();
                                // check for other resources which might be have the same translation, then append the translations in this bundle to them instead of creating a duplicate resource. 
                                bool readId = false;
                                if(entry.Value.ContainsKey(application.DefaultLanguage)) {
                                    cmdFindSimilar.Parameters["@translation"].Value = entry.Value[application.DefaultLanguage];
                                    reader = cmdFindSimilar.ExecuteReader();
                                    if(reader.HasRows) { // translation of current element matches one already in the database, use the existing resource ID
                                        reader.Read();
                                        resourceId = reader.GetInt32(0);
                                        readId = true;
                                    }
                                    reader.Close();
                                }
                                if(!readId) { // no matching resources, find the next available resource ID
                                    using(DbDataReader maxIdReader = cmdMaxRow.ExecuteReader(CommandBehavior.SingleRow)) {
                                        if(maxIdReader.HasRows) {
                                            maxIdReader.Read();
                                            int.TryParse(maxIdReader[0].ToString(), out resourceId);
                                            resourceId++;
                                        }
                                        maxIdReader.Close();
                                    }
                                }
                                cmdInsertElement.Parameters["@tId"].Value = resourceId;
                                cmdInsertElement.ExecuteNonQuery();
                            }

                            cmdInsertResource.Parameters["@tId"].Value = resourceId;
                            foreach(KeyValuePair<string, string> resource in entry.Value) { // <language, translation>>
                                cmdInsertResource.Parameters["@language"].Value = resource.Key;
                                cmdInsertResource.Parameters["@translation"].Value = resource.Value;
                                if(thisElementData.TranslatorsComments.ContainsKey(resource.Key)) {
                                    cmdInsertResource.Parameters["@comment"].Value = thisElementData.TranslatorsComments[resource.Key];
                                } else {
                                    cmdInsertResource.Parameters["@comment"].Value = DBNull.Value;
                                }
                                try {
                                    cmdInsertResource.ExecuteNonQuery();
                                    numResources++;
                                } catch(Exception ex) {
                                    log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                                }
                            }
                            if(!entry.Value.ContainsKey(application.DefaultLanguage) && thisElementData.UntranslatedString.Length > 0) { // the default language hasn't been added, use the untranslated string (msgid) from the PO file
                                cmdInsertResource.Parameters["@language"].Value = application.DefaultLanguage;
                                cmdInsertResource.Parameters["@translation"].Value = thisElementData.UntranslatedString;
                                try {
                                    cmdInsertResource.ExecuteNonQuery();
                                    numResources++;
                                } catch(Exception ex) {
                                    log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                                }
                            }

                            if(thisElementData.TranslatedPlurals != null) {
                                foreach(KeyValuePair<int, Dictionary<string, string>> plural in thisElementData.TranslatedPlurals) {
                                    if(plural.Key == 0) {
                                        continue;
                                    }
                                    string pluralName = elementName + "_" + plural.Key;
                                    cmdCheckExisting.Parameters["@element"].Value = pluralName;
                                    cmdInsertElement.Parameters["@element"].Value = pluralName;
                                    reader = cmdCheckExisting.ExecuteReader();
                                    if(reader.HasRows) { // the element already exists, overwrite existing languages with the ones from the bundle
                                        reader.Read();
                                        int.TryParse(reader[0].ToString(), out resourceId);
                                    }// can't check for similar because the PO format only supports one untranslated plural, so translated plural 2 would overwrite it
                                    reader.Close();
                                    using(DbDataReader maxIdReader = cmdMaxRow.ExecuteReader(CommandBehavior.SingleRow)) {
                                        if(maxIdReader.HasRows) {
                                            maxIdReader.Read();
                                            int.TryParse(maxIdReader[0].ToString(), out resourceId);
                                            resourceId++;
                                        }
                                        maxIdReader.Close();
                                    }
                                    cmdInsertResource.Parameters["@tId"].Value = resourceId;
                                    cmdInsertElement.Parameters["@tId"].Value = resourceId;
                                    try {
                                        cmdInsertElement.ExecuteNonQuery();
                                    } catch(Exception ex) {
                                        log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                                    }

                                    if(!plural.Value.ContainsKey(application.DefaultLanguage)) {
                                        cmdInsertResource.Parameters["@language"].Value = application.DefaultLanguage;
                                        cmdInsertResource.Parameters["@translation"].Value = thisElementData.UntranslatedPlural;
                                        try {
                                            cmdInsertResource.ExecuteNonQuery();
                                            numResources++;
                                        } catch(Exception ex) {
                                            log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                                        }
                                    }

                                    foreach(KeyValuePair<string, string> translatedPlural in plural.Value) { // <language, plural>>
                                        cmdInsertResource.Parameters["@language"].Value = translatedPlural.Key;
                                        cmdInsertResource.Parameters["@translation"].Value = translatedPlural.Value;
                                        try {
                                            cmdInsertResource.ExecuteNonQuery();
                                            numResources++;
                                        } catch(Exception ex) {
                                            log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
                                        }
                                    }
                                }
                            }
                        }
                        numBundles++;
                    }
                }
                log(new StatusMessage(string.Format("Imported {0} resources from {1} bundles containing {2} elements.", numResources, numBundles, numElements), StatusMessage.StatusMessageType.Success));
                trans.Commit();
            }
        } catch(Exception ex) {
            log(new StatusMessage(ex.Message, StatusMessage.StatusMessageType.Error));
        } finally {
            if(db != null) {
                try {
                    db.CloseConnection();
                } catch { }
            }
        }
    }
}